تنسيق البيانات داخل جداول بيانات جوجل باستخدام Apps Script

تُعد جداول بيانات جوجل (Google Sheets) واحدة من الأدوات الموثوقة والمستخدمة على نطاق واسع في العديد من المجالات مثل الأعمال، والتعليم، والتحليل، والإدارة. تقدم هذه الأداة العديد من الخصائص المفيدة التي تسهل جمع البيانات وتحليلها وتنسيقها بطريقة فعالة. ومن أبرز الأدوات التي توفرها جداول بيانات جوجل هي Apps Script، وهي لغة برمجة قائمة على JavaScript تتيح للمستخدمين تخصيص جداول بيانات جوجل بشكل كامل من خلال إنشاء سكربتات مخصصة لأداء مهام معينة مثل التنسيق التلقائي للبيانات، والتحقق من صحتها، وتنظيمها بما يتناسب مع احتياجات المستخدم.

ما هو Apps Script؟

Google Apps Script هو بيئة تطوير بسيطة تتيح للمستخدمين تخصيص تطبيقات جوجل المختلفة، بما في ذلك جداول بيانات جوجل، باستخدام لغة JavaScript. يمكن استخدام Apps Script لتوسيع وظيفة جداول البيانات، مثل إضافة صيغ مخصصة، وإنشاء واجهات تفاعلية، وتنسيق الخلايا بشكل تلقائي، وتحسين العمليات الحسابية.

إن إنشاء السكربتات باستخدام Apps Script يسمح للمستخدمين بأتمتة العديد من المهام المتكررة مثل تنسيق النصوص، أو ضبط ألوان الخلايا بناءً على قيم معينة، أو حتى دمج البيانات بين عدة أوراق عمل. هذه الخصائص تجعل من Google Apps Script أداة قوية لزيادة الإنتاجية والتقليل من الجهد اليدوي.

الأساسيات: إنشاء سكربت جديد في جداول بيانات جوجل

لبداية العمل مع Apps Script داخل جداول بيانات جوجل، يجب أولاً فتح Google Sheets والانتقال إلى الأدوات في شريط القائمة العلوي واختيار “محرر السكربتات” (Script Editor). بعد ذلك، ستفتح نافذة جديدة لكتابة السكربتات المخصصة.

في هذا المحرر، يمكنك كتابة الأكواد الخاصة بك باستخدام لغة JavaScript مع إضافة مكتبات معينة يمكنها التفاعل مع جداول البيانات. على سبيل المثال، يمكنك استخدام الكود التالي للوصول إلى جدول بيانات معين:

javascript
CopyEdit
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange("A1").setValue("Hello, Google Sheets!");
}

يقوم هذا السكربت بإدخال النص “Hello, Google Sheets!” في الخلية A1 في الجدول الذي يعمل عليه المستخدم حاليًا.

التنسيق التلقائي باستخدام Apps Script

يعد التنسيق التلقائي أحد المهام الشائعة التي يمكن أتمتتها باستخدام Apps Script. يمكن تنسيق البيانات وفقًا لمجموعة من المعايير مثل القيم الرقمية، أو النصوص، أو التواريخ. يمكن تخصيص التنسيق لتشمل تغييرات في الألوان، الخطوط، المحاذاة، والخلايا المدمجة، وغيرها من التعديلات.

1. تنسيق الخلايا بناءً على القيم

لنأخذ على سبيل المثال أنك ترغب في تنسيق الخلايا بحيث يتم تلوين الخلايا التي تحتوي على قيم أكبر من 100 بلون أخضر، بينما الخلايا التي تحتوي على قيم أقل من 50 تُلون باللون الأحمر. يمكن تنفيذ ذلك باستخدام الكود التالي:

javascript
CopyEdit
function formatCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  
  for (var i = 1; i <= range.getNumRows(); i++) {
    var cellValue = range.getCell(i, 1).getValue();
    if (cellValue > 100) {
      range.getCell(i, 1).setBackground('green');
    } else if (cellValue < 50) {
      range.getCell(i, 1).setBackground('red');
    }
  }
}

يقوم هذا السكربت بمراجعة كل قيمة في العمود الأول من البيانات وإذا كانت القيمة أكبر من 100، يتم تلوين الخلية باللون الأخضر، وإذا كانت أقل من 50، يتم تلوينها باللون الأحمر.

2. تنسيق النصوص والخلايا

يمكن أيضًا تنسيق النصوص داخل الخلايا مثل تغيير الخط، الحجم، اللون، والمحاذاة. لتغيير النص داخل الخلية ليكون عريضًا و مائلًا، يمكن استخدام الكود التالي:

javascript
CopyEdit
function formatText() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A1:A10");
  range.setFontWeight("bold");
  range.setFontStyle("italic");
}

هذا السكربت يغير النص في الخلايا من A1 إلى A10 ليكون عريضًا و مائلًا.

3. التنسيق باستخدام التنسيقات الشرطية

التنسيق الشرطي هو أحد الأساليب القوية في جداول بيانات جوجل حيث يمكن استخدامه لتغيير مظهر الخلايا بناءً على القيم التي تحتوي عليها. يمكن استخدام Apps Script لإنشاء تنسيق شرطي بشكل برمجي.

مثال على ذلك هو تلوين الخلايا التي تحتوي على تواريخ ماضية باللون الرمادي:

javascript
CopyEdit
function conditionalFormatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("B1:B100");
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenDateBefore(new Date())  // تاريخ اليوم
    .setBackground("#D3D3D3")    // اللون الرمادي
    .setRanges([range])
    .build();
  
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

هنا يتم تحديد تنسيق شرطي لتغيير خلفية الخلايا في النطاق B1:B100 إلى اللون الرمادي إذا كانت التواريخ في تلك الخلايا أقل من تاريخ اليوم.

التفاعل مع المستخدم من خلال النوافذ المنبثقة (Dialogs)

يمكن استخدام Apps Script لإنشاء نوافذ منبثقة (dialogs) لجعل التفاعل مع المستخدم أسهل وأكثر فاعلية. يمكن لهذه النوافذ المنبثقة أن تعرض رسائل تحذيرية أو تطلب من المستخدم إدخال بيانات.

مثال بسيط هو عرض نافذة منبثقة تحتوي على رسالة للمستخدم:

javascript
CopyEdit
function showDialog() {
  var htmlOutput = HtmlService.createHtmlOutput('Welcome to Google Sheets!');
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Greeting');
}

يتم عرض هذه الرسالة داخل نافذة منبثقة عند تشغيل السكربت.

التعامل مع الأوراق المتعددة

في جداول بيانات جوجل، يمكن أن تحتوي الورقة على العديد من الأوراق (Sheets)، ويمكنك استخدام Apps Script للتعامل مع هذه الأوراق بشكل مستقل. يمكن للمستخدم استخدام السكربت لتحديد الورقة التي يريد العمل معها أو لتنفيذ عمليات عبر عدة أوراق في وقت واحد.

مثال على ذلك هو إضافة البيانات من ورقة إلى أخرى:

javascript
CopyEdit
function copyData() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source");
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Target");
  
  var range = sourceSheet.getRange("A1:B10");
  var data = range.getValues();
  
  targetSheet.getRange("A1:B10").setValues(data);
}

يتم نسخ البيانات من الورقة المسماة “Source” إلى الورقة المسماة “Target” في نفس جدول البيانات.

أتمتة المهام باستخدام Triggers

يمكنك استخدام Triggers في Apps Script لتشغيل السكربتات تلقائيًا بناءً على أحداث معينة. على سبيل المثال، يمكن تعيين Trigger ليتم تشغيله عند تغيير البيانات في جدول البيانات، أو عند تحرير الخلايا.

إليك مثال على كيفية إضافة Trigger لتشغيل السكربت عند تعديل البيانات في الجدول:

javascript
CopyEdit
function createTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('onEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

function onEdit(e) {
  // يتم تنفيذ هذا السكربت عندما يتم تعديل أي خلية في الجدول
  var range = e.range;
  var editedValue = range.getValue();
  Logger.log('تم تعديل القيمة في الخلية: ' + range.getA1Notation() + ' إلى ' + editedValue);
}

الخلاصة

Google Apps Script هو أداة قوية تتيح للمستخدمين تخصيص جداول بيانات جوجل بسهولة وتنسيق البيانات بشكل فعال. من خلال استخدام هذه الأداة، يمكن أتمتة العديد من المهام المعقدة مثل تنسيق الخلايا بناءً على القيم، وإضافة التنسيق الشرطي، وإنشاء نوافذ منبثقة للتفاعل مع المستخدمين، والتعامل مع الأوراق المتعددة.

توفر Apps Script إمكانيات هائلة لتخصيص جداول بيانات جوجل بطريقة لا حصر لها، مما يسهل عملية إدارة البيانات وتحليلها بشكل دقيق وفعال.